using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace 药品管理系统
{
    public partial class frm_Churu : Form
    {
        private DataTable StorageTable;
        private DataTable DeliveryTable;
        private DataView StorageViewByName;
        private DataView DeliveryViewByName;
        public frm_Churu()
        {
            InitializeComponent();
            this.StartPosition = FormStartPosition.CenterScreen;
            this.dgv_Ru.AllowUserToAddRows = false;
            this.dgv_Ru.RowHeadersVisible = false;
            this.dgv_Ru.BackgroundColor = Color.White;
            this.dgv_Ru.AutoSizeColumnsMode =
                DataGridViewAutoSizeColumnsMode.AllCells;
         
            this.StartPosition = FormStartPosition.CenterScreen;                                             
            this.dgv_Chu.AllowUserToAddRows = false;                                                     
            this.dgv_Chu.RowHeadersVisible = false;                                                     
            this.dgv_Chu.BackgroundColor = Color.White;                                                 
            this.dgv_Chu.AutoSizeColumnsMode =
                DataGridViewAutoSizeColumnsMode.AllCells;                                                 
        }

        private void 菜单ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            frm_Menu a = new frm_Menu();
            a.Show();
            this.Hide();
        }

        private void 登录ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            frm_Login b = new frm_Login();
            b.Show();
            this.Hide();
        }

        private void 注册ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            frm_Register c = new frm_Register();
            c.Show();
            this.Hide();
        }

        private void btn_Query_Click(object sender, EventArgs e)
        {
            SqlConnection sqlConnection = new SqlConnection();
            sqlConnection.ConnectionString =
               "Server=KK的秘密;Database=MMS;Integrated Security=sspi"; 
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.Connection = sqlConnection;
            sqlCommand.CommandText = "SELECT * FROM tb_storage;";
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
            sqlDataAdapter.SelectCommand = sqlCommand;
            sqlDataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;   
            DataTable storageTable = new DataTable();
            sqlConnection.Open();
            sqlDataAdapter.Fill(storageTable);
            sqlConnection.Close();
            this.StorageTable = storageTable;
            this.StorageViewByName = new DataView();
            this.StorageViewByName.Table = this.StorageTable;
            this.StorageViewByName.Sort = "Mname ASC"; 
            this.dgv_Ru.Columns.Clear();
            this.dgv_Ru.DataSource = storageTable;
            this.dgv_Ru.Columns["Mno"].HeaderText = "药品编号";
            this.dgv_Ru.Columns["Mname"].HeaderText = "药品名";
            this.dgv_Ru.Columns["Sname"].HeaderText = "供应商";
            this.dgv_Ru.Columns["Stdate"].HeaderText = "入库日期";
            this.dgv_Ru.Columns["Stvalidity"].HeaderText = "有效日期";
            this.dgv_Ru.Columns["Stno"].HeaderText="入库编号";
        }

        private void btn_Add_Click(object sender, EventArgs e)
        {
            SqlConnection sqlConnection = new SqlConnection();                                          
            sqlConnection.ConnectionString =
                "Server=KK的秘密;Database=MMS;Integrated Security=sspi";                        
            SqlCommand sqlCommand = sqlConnection.CreateCommand();                                      
            sqlCommand.CommandText =
                "INSERT tb_storage (Stno,Mno,Mname,Sname,Stdate,Stvalidity) VALUES(@Stno,@Mno,@Mname,@Sname,@Stdate,@Stvalidity);";
            sqlCommand.Parameters.AddWithValue("@Stno", this.txb_Ru.Text.Trim());   
            sqlCommand.Parameters.AddWithValue("@Mno", this.txb_Mno.Text.Trim());         
            sqlCommand.Parameters.AddWithValue("@Mname", this.txb_Mname.Text.Trim());
            sqlCommand.Parameters.AddWithValue("@Sname", this.cmb_Sname.Text.Trim());
            sqlCommand.Parameters.AddWithValue("@Stdate", this.dtp_Stdate.Text.Trim());
            sqlCommand.Parameters.AddWithValue("@Stvalidity", this.dtp_Stvalidity.Text.Trim());
            
            sqlConnection.Open();                                                                       
            int rowAffected = sqlCommand.ExecuteNonQuery();                                             
            sqlConnection.Close();                                                                      
            if (rowAffected == 1)                                                                      
            {
                MessageBox.Show("添加记录成功");                                                          
            }
            else                                                                                        
            {
                MessageBox.Show("添加记录失败");                                                        
            }  
        }

        private void btn_Delete_Click(object sender, EventArgs e)
        {
            SqlConnection sqlConnection = new SqlConnection();                                              
            sqlConnection.ConnectionString =
                "Server=KK的秘密;Database=MMS;Integrated Security=sspi";                               
            SqlCommand deleteCommand = new SqlCommand();                                                    
            deleteCommand.Connection = sqlConnection;                                                       
            deleteCommand.CommandText =                                                                     
                "DELETE tb_storage "
                + " WHERE Stno='" + txb_Ru.Text + "';";
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();                                                                      
            sqlDataAdapter.DeleteCommand = deleteCommand;                                                   
            DataTable StorageTable = (DataTable)this.dgv_Ru.DataSource;                                  
            sqlConnection.Open();
            int rowAffected = deleteCommand.ExecuteNonQuery();                                           
            sqlConnection.Close();                                                                          
            MessageBox.Show("删除" + rowAffected.ToString() + "行。");
            sqlConnection.Close();  
        }

        private void btn_Update_Click(object sender, EventArgs e)
        {
            SqlConnection sqlConnection = new SqlConnection();
            sqlConnection.ConnectionString =
                "Server=KK的秘密;Database=MMS;Integrated Security=sspi"; 
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.Connection = sqlConnection;
            sqlCommand.CommandText =
                "UPDATE tb_storage"
                + " SET Mno=@Mno,Mname=@Mname,Sname=@Sname,Stdate=@Stdate,Stvalidity=@Stvalidity"
                + " WHERE Stno=@Stno;";
            sqlCommand.Parameters.Add("@Mname", SqlDbType.VarChar,20, "Mname");
            sqlCommand.Parameters.Add("@Sname", SqlDbType.VarChar, 20, "Sname");
            sqlCommand.Parameters.Add("@Stdate", SqlDbType.VarChar, 20, "Stdate");
            sqlCommand.Parameters.Add("@Stvalidity", SqlDbType.VarChar, 20, "Stvalidity");
            sqlCommand.Parameters.Add("@Mno", SqlDbType.Char, 4, "Mno");
            sqlCommand.Parameters.Add("@Stno", SqlDbType.Char, 4, "Stno");
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();                                           
            sqlDataAdapter.UpdateCommand = sqlCommand;                                                      
            DataTable storageTable = this.StorageTable;                                                     
            sqlConnection.Open();
            int rowAffected = sqlDataAdapter.Update(storageTable);                                          
            sqlConnection.Close();                                                                          
            MessageBox.Show("更新" + rowAffected.ToString() + "行。"); 
        }

        private void btn_Query1_Click(object sender, EventArgs e)
        {
            SqlConnection sqlConnection = new SqlConnection();
            sqlConnection.ConnectionString =
               "Server=KK的秘密;Database=MMS;Integrated Security=sspi"; 
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.Connection = sqlConnection;
            sqlCommand.CommandText = "SELECT * FROM tb_delivery;";
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
            sqlDataAdapter.SelectCommand = sqlCommand;
            sqlDataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;   
            DataTable deliveryTable = new DataTable();
            sqlConnection.Open();
            sqlDataAdapter.Fill(deliveryTable);
            sqlConnection.Close();
            this.DeliveryTable = deliveryTable;
            this.DeliveryViewByName = new DataView();
            this.DeliveryViewByName.Table = this.DeliveryTable;
            this.DeliveryViewByName.Sort = "Mname ASC"; 
            this.dgv_Chu.Columns.Clear();
            this.dgv_Chu.DataSource = deliveryTable;
            this.dgv_Chu.Columns["Mno"].HeaderText = "药品编号";
            this.dgv_Chu.Columns["Mname"].HeaderText = "药品名";
            this.dgv_Chu.Columns["Sname"].HeaderText = "供应商";
            this.dgv_Chu.Columns["Ddate"].HeaderText = "出库日期";
            this.dgv_Chu.Columns["Dvalidity"].HeaderText = "有效日期";
            this.dgv_Chu.Columns["Dno"].HeaderText = "出库编号";
        }

        private void btn_Add1_Click(object sender, EventArgs e)
        {
            SqlConnection sqlConnection = new SqlConnection();
            sqlConnection.ConnectionString =
                "Server=KK的秘密;Database=MMS;Integrated Security=sspi"; 
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText =
                "INSERT tb_delivery (Dno,Mno,Mname,Sname,Ddate,Dvalidity) VALUES(@Dno,@Mno,@Mname,@Sname,@Ddate,@Dvalidity);";
            sqlCommand.Parameters.AddWithValue("@Dno", this.txb_Chu.Text.Trim());
            sqlCommand.Parameters.AddWithValue("@Mno", this.txb_Mno1.Text.Trim());
            sqlCommand.Parameters.AddWithValue("@Mname", this.txb_Mname1.Text.Trim());
            sqlCommand.Parameters.AddWithValue("@Sname", this.cmb_Sname1.Text.Trim());
            sqlCommand.Parameters.AddWithValue("@Ddate", this.dtp_Ddate.Text.Trim());
            sqlCommand.Parameters.AddWithValue("@Dvalidity", this.dtp_Dvalidity.Text.Trim());

            sqlConnection.Open();
            int rowAffected = sqlCommand.ExecuteNonQuery();
            sqlConnection.Close();
            if (rowAffected == 1)
            {
                MessageBox.Show("添加记录成功");
            }
            else
            {
                MessageBox.Show("添加记录失败");
            }  
        }

        private void btn_Delete1_Click(object sender, EventArgs e)
        {
            SqlConnection sqlConnection = new SqlConnection();
            sqlConnection.ConnectionString =
               "Server=KK的秘密;Database=MMS;Integrated Security=sspi"; 
            SqlCommand deleteCommand = new SqlCommand();
            deleteCommand.Connection = sqlConnection;
            deleteCommand.CommandText =
                "DELETE tb_delivery "
                + " WHERE Dno='" + txb_Chu.Text + "';";
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
            sqlDataAdapter.DeleteCommand = deleteCommand;
            DataTable StorageTable = (DataTable)this.dgv_Chu.DataSource;
            sqlConnection.Open();
            int rowAffected = deleteCommand.ExecuteNonQuery();
            sqlConnection.Close();
            MessageBox.Show("删除" + rowAffected.ToString() + "行。");
            sqlConnection.Close();  
        }

        private void btn_Update1_Click(object sender, EventArgs e)
        {
            SqlConnection sqlConnection = new SqlConnection();
            sqlConnection.ConnectionString =
               "Server=KK的秘密;Database=MMS;Integrated Security=sspi"; 
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.Connection = sqlConnection;
            sqlCommand.CommandText =
                "UPDATE tb_delivery"
                + " SET Mno=@Mno,Mname=@Mname,Sname=@Sname,Ddate=@Ddate,Dvalidity=@Dvalidity"
                + " WHERE Dno=@Dno;";
            sqlCommand.Parameters.Add("@Mname", SqlDbType.VarChar, 20, "Mname");
            sqlCommand.Parameters.Add("@Sname", SqlDbType.VarChar, 20, "Sname");
            sqlCommand.Parameters.Add("@Ddate", SqlDbType.VarChar, 20, "Ddate");
            sqlCommand.Parameters.Add("@Dvalidity", SqlDbType.VarChar, 20, "Dvalidity");
            sqlCommand.Parameters.Add("@Mno", SqlDbType.Char, 4, "Mno");
            sqlCommand.Parameters.Add("@Dno", SqlDbType.Char, 4, "Dno");
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
            sqlDataAdapter.UpdateCommand = sqlCommand;
            DataTable deliveryTable = this.DeliveryTable;
            sqlConnection.Open();
            int rowAffected = sqlDataAdapter.Update(deliveryTable);
            sqlConnection.Close();
            MessageBox.Show("更新" + rowAffected.ToString() + "行。"); 
        }

        private void dgv_Ru_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            int a = new int();
            a = dgv_Ru.CurrentRow.Index;
            txb_Mno.Text = dgv_Ru.Rows[a].Cells["Mno"].Value.ToString();
            txb_Mname.Text = dgv_Ru.Rows[a].Cells["Mname"].Value.ToString();
            txb_Ru.Text = dgv_Ru.Rows[a].Cells["Stno"].Value.ToString();
            cmb_Sname.Text = dgv_Ru.Rows[a].Cells["Sname"].Value.ToString();
            dtp_Stdate.Text = dgv_Ru.Rows[a].Cells["Stdate"].Value.ToString();
            dtp_Stvalidity.Text = dgv_Ru.Rows[a].Cells["Stvalidity"].Value.ToString();
        }

        private void dgv_Chu_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            int a = new int();
            a = dgv_Chu.CurrentRow.Index;
            txb_Mno1.Text = dgv_Chu.Rows[a].Cells["Mno"].Value.ToString();
            txb_Mname1.Text = dgv_Chu.Rows[a].Cells["Mname"].Value.ToString();
            txb_Chu.Text = dgv_Chu.Rows[a].Cells["Dno"].Value.ToString();
            cmb_Sname1.Text = dgv_Chu.Rows[a].Cells["Sname"].Value.ToString();
            dtp_Ddate.Text = dgv_Chu.Rows[a].Cells["Ddate"].Value.ToString();
            dtp_Dvalidity.Text = dgv_Chu.Rows[a].Cells["Dvalidity"].Value.ToString();
        }

        private void btn_SearchByName_Click(object sender, EventArgs e)
        {
            DataRowView[] searchResultRowViews =
               this.StorageViewByName.FindRows(this.txb_1.Text.Trim());                           
            DataTable searchResultTable = this.StorageTable.Clone();                                         
            foreach (DataRowView dataRowView in searchResultRowViews)                                       
            {
                searchResultTable.ImportRow(dataRowView.Row);                                               
            }
            this.dgv_Ru.DataSource = searchResultTable; 
        }

        private void btn_SearchByNo_Click(object sender, EventArgs e)
        {
            DataRow searchResultRow = this.StorageTable.Rows.Find(this.txb_2.Text.Trim());            
            DataTable searchResultTable = this.StorageTable.Clone();                                         
            searchResultTable.ImportRow(searchResultRow);                                                   
            this.dgv_Ru.DataSource = searchResultTable;    
        }

        private void btn_SearchByNo1_Click(object sender, EventArgs e)
        {
            DataRow searchResultRow = this.DeliveryTable.Rows.Find(this.txb_3.Text.Trim());
            DataTable searchResultTable = this.DeliveryTable.Clone();
            searchResultTable.ImportRow(searchResultRow);
            this.dgv_Chu.DataSource = searchResultTable;  
        }

        private void btn_SearchByName1_Click(object sender, EventArgs e)
        {
            DataRowView[] searchResultRowViews =
              this.DeliveryViewByName.FindRows(this.txb_4.Text.Trim());
            DataTable searchResultTable = this.DeliveryTable.Clone();
            foreach (DataRowView dataRowView in searchResultRowViews)
            {
                searchResultTable.ImportRow(dataRowView.Row);
            }
            this.dgv_Chu.DataSource = searchResultTable; 
        }

        private void btn_Outdate_Click(object sender, EventArgs e)
        {
            if (DateTime.Parse(txb_Date.Text) > DateTime.Parse(dtp_Stvalidity.Text))
            {
                DialogResult dr = MessageBox.Show("药品" + txb_Mno.Text + "过期，请及时处理。是否补货？", "系统提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Question);
                if (dr == DialogResult.OK)
                {
                    frm_Order a = new frm_Order();
                    a.Show();
                    this.Hide();
                }
                else
                { }
            }
            else
            {
                MessageBox.Show("药品未过期");
            }
        }

        private void frm_Churu_Load(object sender, EventArgs e)
        {
            this.txb_Date.Text = DateTime.Now.ToShortDateString();
        }

    }
}
